/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package da;

import entities.Branch;
import entities.FineLoan;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Bich Tran Van
 */
public class DABranch {

    MSSQLConnection conn = null;

    public DABranch() {
        try {
            conn = new MSSQLConnection();
            conn.getConnection();
        } catch (Exception ex) {
            Logger.getLogger(DALoanType.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    // get all loan type
    public ArrayList<Branch> getAll() throws Exception {
        ArrayList<Branch> list = new ArrayList<Branch>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL View_all_Branch}");
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Branch branch = new Branch();
                branch.setBranchId(conn.rs.getInt("BranchID"));
                branch.setBranchName(conn.rs.getString("BranchName"));
                branch.setAddress(conn.rs.getString("Address"));
                branch.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                branch.setDescription(conn.rs.getString("Description"));
                branch.setStatus(conn.rs.getBoolean("Status"));
                list.add(branch);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    
    public ArrayList<Branch> getAllActive() throws Exception {
        ArrayList<Branch> list = new ArrayList<Branch>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL View_Branch_Active}");
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Branch branch = new Branch();
                branch.setBranchId(conn.rs.getInt("BranchID"));
                branch.setBranchName(conn.rs.getString("BranchName"));
                branch.setAddress(conn.rs.getString("Address"));
                branch.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                branch.setDescription(conn.rs.getString("Description"));
                branch.setStatus(conn.rs.getBoolean("Status"));
                list.add(branch);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    

    //get loan type by id
    public ArrayList<Branch> getByID(int id) throws Exception {
        ArrayList<Branch> list = new ArrayList<Branch>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL View_Branch_By_ID(?)}");
            conn.cstm.setInt(1, id);
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Branch branch = new Branch();
                branch.setBranchId(conn.rs.getInt("BranchID"));
                branch.setBranchName(conn.rs.getString("BranchName"));
                branch.setAddress(conn.rs.getString("Address"));
                branch.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                branch.setDescription(conn.rs.getString("Description"));
                branch.setStatus(conn.rs.getBoolean("Status"));
                list.add(branch);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public ArrayList<Branch> getByName(String name) throws Exception {
        ArrayList<Branch> list = new ArrayList<Branch>();
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL View_Branch_By_Name(?)}");
            conn.cstm.setString(1, name);
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                Branch branch = new Branch();
                branch.setBranchId(conn.rs.getInt("BranchID"));
                branch.setBranchName(conn.rs.getString("BranchName"));
                branch.setAddress(conn.rs.getString("Address"));
                branch.setPhoneNumber(conn.rs.getString("PhoneNumber"));
                branch.setDescription(conn.rs.getString("Description"));
                branch.setStatus(conn.rs.getBoolean("Status"));
                list.add(branch);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    //insert to Loan type
    public int insert(String name , String address , String phoneNumber , String description,boolean status) throws Exception {
        int result = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{ CALL Insert_Branch(?,?,?,?,?) }");
            conn.cstm.setString(1, name);
            conn.cstm.setString(2, address);
            conn.cstm.setString(3, phoneNumber);
            conn.cstm.setString(4, description);
            conn.cstm.setBoolean(5, status);
            result = conn.cstm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    //update to loan type
    public int update(Branch branch) throws Exception {
        int result = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL Update_Branch(?,?,?,?,?,?)}");
            conn.cstm.setInt(1, branch.getBranchId());
            conn.cstm.setString(2, branch.getBranchName());
            conn.cstm.setString(3, branch.getAddress());
            conn.cstm.setString(4, branch.getPhoneNumber());
            conn.cstm.setString(5, branch.getDescription());
            conn.cstm.setBoolean(6, branch.isStatus());
            result = conn.cstm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    //delete from loan type
    public int Disable(int id) throws Exception {
        int result = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL Disable_Branch(?)}");
            conn.cstm.setInt(1, id);
            result = conn.cstm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }
    
    public int Enable(int id) throws Exception {
        int result = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL Enable_Branch(?)}");
            conn.cstm.setInt(1, id);
            result = conn.cstm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }
}
